﻿using Dapper;
using Domain.Entitys;
using Domain.Repositorys;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys
{
    public class CFTKRepository : ICFTKRepository
    {
        private readonly MyDbContext myDbContext;

        public CFTKRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        /// <summary>
        /// 级联显示下拉框数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<Cascader> Cascaders()
        {
            List<CFFK> list = myDbContext.CFFKs.ToList();//获取1级级结构表数据
            List<Cascader> list2 = new List<Cascader>();
            foreach (var c in list)
            {
                //获取2级数据
                List<CFSK> list3 = myDbContext.CFSKs.Where(e => e.FirstKindId == c.FirstKindId).ToList();
                List<Cascader> list4 = new List<Cascader>();
                foreach (var item in list3)
                {
                    Cascader cascader1 = new Cascader()
                    {
                        label = item.SecondKindName,
                        value = item.SecondKindId
                    };
                    list4.Add(cascader1);
                }
                Cascader cascader = new Cascader()
                {
                    value = c.FirstKindId,
                    label = c.FirstKindName,
                    children = list4
                };
                list2.Add(cascader);
            }
            return list2;
        }

        /// <summary>
        /// 级联显示下拉框数据2
        /// </summary>
        /// <returns></returns>
        public List<Cascader> Cascaders2()
        {
            List<CFFK> list = myDbContext.CFFKs.ToList();//获取1级级结构表数据
            List<Cascader> list2 = new List<Cascader>();
            foreach (var c in list)
            {
                //获取2级数据
                List<CFSK> list3 = myDbContext.CFSKs.Where(e => e.FirstKindId == c.FirstKindId).ToList();
                List<Cascader> list4 = new List<Cascader>();
                foreach (var item in list3)
                {
                    //获取3级数据
                    List<CFTK> cFTKs = myDbContext.CFTKs.Where(e => e.SecondKindId == item.SecondKindId).ToList();
                    List<Cascader> cascaders = new List<Cascader>();
                    foreach (var item1 in cFTKs)
                    {
                        Cascader cascader2 = new Cascader()
                        {
                            label = item1.ThirdKindName,
                            value = item1.ThirdKindId
                        };
                        cascaders.Add(cascader2);
                    }
                    Cascader cascader1 = new Cascader()
                    {
                        label = item.SecondKindName,
                        value = item.SecondKindId,
                        children = cascaders
                    };
                    list4.Add(cascader1);
                }

                Cascader cascader = new Cascader()
                {
                    value = c.FirstKindId,
                    label = c.FirstKindName,
                    children = list4
                };
                list2.Add(cascader);
            }
            return list2;
        }

        /// <summary>
        /// 删除3级机构表数据
        /// </summary>
        /// <param name="cFTK"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> CFTKDeleteAsync(CFTK cFTK)
        {
            myDbContext.CFTKs.Remove(cFTK);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 添加3级机构表数据
        /// </summary>
        /// <param name="cFTK"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> CFTKInsertAsync(CFTK cFTK)
        {
            await myDbContext.CFTKs.AddAsync(cFTK);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据3级编号查询对应数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<CFTK> CFTKSelectByCftid(string id)
        {
            return await myDbContext.CFTKs.FirstOrDefaultAsync(c => c.ThirdKindId == id);
        }

        /// <summary>
        /// 按id查询对应数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<CFTK> CFTKSelectById(int id)
        {
            return await myDbContext.CFTKs.FirstOrDefaultAsync(c => c.FtkId == id);
        }

        public async Task<List<CFTK>> CFTKSelectCFSKCFFKId(string sid, string fid)
        {
            //return await myDbContext.CFSKs.Where(e => EF.Functions.Like(e.FirstKindId, $"%{id}%")).ToListAsync();
            var num = myDbContext.CFTKs.AsQueryable();
            if (!string.IsNullOrEmpty(sid))
            {
                num = num.Where(e => e.SecondKindId == sid);

            }
            if (!string.IsNullOrEmpty(fid)) {
                num = num.Where(e => e.FirstKindId == fid);
            } 
            return await num.ToListAsync();
        }

        /// <summary>
        /// 分页查询3级机构表数据
        /// </summary>
        /// <param name="fenYe"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<CFTK> CFTKSelectFenYe(FenYe<CFTK> fenYe)
        {
            using (SqlConnection sc = new SqlConnection("Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;"))
            //using (SqlConnection sc = new SqlConnection("Data Source=.;Initial Catalog=HR;Integrated Security=True;Connect Timeout=30;Encrypt=False;"))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows out";
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize",fenYe.PageSize);
                parameters.Add("@keyName", "FtkId");
                parameters.Add("@tabelName", "CFTK");
                parameters.Add("@where", "");
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output,dbType: DbType.Int32);

                List<CFTK> cFTKs = sc.Query<CFTK>(sql,parameters).ToList();

                fenYe.list = cFTKs;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 修改3级结构表数据
        /// </summary>
        /// <param name="cFTK"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> CFTKUpdateAsync(CFTK cFTK)
        {
            myDbContext.CFTKs.Update(cFTK);
            return await myDbContext.SaveChangesAsync();
        }

       

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        async Task<List<CFTK>> ICFTKRepository.CKTKselectAsyns()
        {
            return await myDbContext.CFTKs.ToListAsync();
        }

        public async Task<List<Cascader>> GetCFFKCFSKCFTK()
        {
            List<Cascader> list = new List<Cascader>();
            List<CFFK> cffks = myDbContext.CFFKs.ToList();
            foreach (CFFK cffk in cffks)
            {
                Cascader lj = new Cascader()
                {
                    label = cffk.FirstKindName,
                    value = cffk.FirstKindId,
                    children = GetCFSK(cffk.FirstKindId)
                };
                list.Add(lj);
            }
            return list;
        }

        public List<Cascader> GetCFSK(string id)
        {
            List<Cascader> list = new List<Cascader>();
            List<CFSK> cfsks = myDbContext.CFSKs.Where(e => e.FirstKindId == id).ToList();
            foreach (CFSK cfsk in cfsks)
            {
                Cascader lj = new Cascader()
                {
                    label = cfsk.SecondKindName,
                    value = cfsk.SecondKindId,
                    children = GetCFTK(cfsk.SecondKindId),
                };
                list.Add(lj);
            }
            return list;
        }

        public List<Cascader> GetCFTK(string id)
        {
            List<Cascader> list = new List<Cascader>();
            List<CFTK> cftks = myDbContext.CFTKs.Where(e => e.SecondKindId == id).ToList();
            foreach (CFTK cftk in cftks)
            {
                Cascader lj = new Cascader()
                {
                    label = cftk.ThirdKindName,
                    value = cftk.ThirdKindId,
                    children = null,
                };
                list.Add(lj);
            }
            return list;
        }
    }
}
